package com.hisee.util;

import com.hisee.bean.BS004;
import com.hisee.bean.BS320;
import com.hisee.bean.BSImgBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DbUtil {
	/**
	 * 执行数据库插入操作
	 *
	 * @param valueMap  插入数据表中key为列名和value为列对应的值的Map对象
	 * @param tableName 要插入的数据库的表名
	 * @return 影响的行数
	 * @throws SQLException SQL异常
	 */
	public static int insert(String tableName, Map<String, Object> valueMap) throws Exception {

		/** 获取数据库插入的Map的键值对的值 **/
		Set<String> keySet = valueMap.keySet();
		Iterator<String> iterator = keySet.iterator();
		/** 要插入的字段sql，其实就是用key拼起来的 **/
		StringBuilder columnSql = new StringBuilder();
		/** 要插入的字段值，其实就是？ **/
		StringBuilder unknownMarkSql = new StringBuilder();
		Object[] bindArgs = new Object[valueMap.size()];
		int i = 0;
		while (iterator.hasNext()) {
			String key = iterator.next();
			columnSql.append(i == 0 ? "" : ",");
			columnSql.append(key);

			unknownMarkSql.append(i == 0 ? "" : ",");
			unknownMarkSql.append("?");
			bindArgs[i] = valueMap.get(key);
			i++;
		}
		/** 开始拼插入的sql语句 **/
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ");
		sql.append(tableName);
		sql.append(" (");
		sql.append(columnSql);
		sql.append(" )  VALUES (");
		sql.append(unknownMarkSql);
		sql.append(" )");

		return executeUpdate(sql.toString(), bindArgs);
	}

	/**
	 * 执行更新操作
	 *
	 * @param tableName 表名
	 * @param valueMap  要更改的值
	 * @param whereMap  条件
	 * @return 影响的行数
	 * @throws SQLException SQL异常
	 */
	public static int update(String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap)
			throws Exception {
		/** 获取数据库插入的Map的键值对的值 **/
		Set<String> keySet = valueMap.keySet();
		Iterator<String> iterator = keySet.iterator();
		/** 开始拼插入的sql语句 **/
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(tableName);
		sql.append(" SET ");

		/** 要更改的的字段sql，其实就是用key拼起来的 **/
		StringBuilder columnSql = new StringBuilder();
		int i = 0;
		List<Object> objects = new ArrayList<Object>();
		while (iterator.hasNext()) {
			String key = iterator.next();
			columnSql.append(i == 0 ? "" : ",");
			columnSql.append(key + " = ? ");
			objects.add(valueMap.get(key));
			i++;
		}
		sql.append(columnSql);

		/** 更新的条件:要更改的的字段sql，其实就是用key拼起来的 **/
		StringBuilder whereSql = new StringBuilder();
		int j = 0;
		if (whereMap != null && whereMap.size() > 0) {
			whereSql.append(" WHERE ");
			iterator = whereMap.keySet().iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				whereSql.append(j == 0 ? "" : " AND ");
				whereSql.append(key + " = ? ");
				objects.add(whereMap.get(key));
				j++;
			}
			sql.append(whereSql);
		}
		return executeUpdate(sql.toString(), objects.toArray());
	}

	/**
	 * 可以执行新增，修改，删除
	 *
	 * @param sql      sql语句
	 * @param bindArgs 绑定参数
	 * @return 影响的行数
	 * @throws Exception
	 */
	public static int executeUpdate(String sql, Object[] bindArgs) throws Exception {
		/** 影响的行数 **/
		int affectRowCount = -1;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/** 从数据库连接池中获取数据库连接 **/
			connection = DBConnectionPool.getInstance().getConnection();
			/** 执行SQL预编译 **/
			preparedStatement = connection.prepareStatement(sql.toString());
			/** 设置不自动提交，以便于在出现异常的时候数据库回滚 **/
			connection.setAutoCommit(false);
			// System.out.println(getExecSQL(sql, bindArgs));
			if (bindArgs != null) {
				/** 绑定参数设置sql占位符中的值 **/
				for (int i = 0; i < bindArgs.length; i++) {
					preparedStatement.setObject(i + 1, bindArgs[i]);
				}
			}
			/** 执行sql **/
			affectRowCount = preparedStatement.executeUpdate();
			connection.commit();
			String operate;
			if (sql.toUpperCase().indexOf("DELETE FROM") != -1) {
				operate = "删除";
			} else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) {
				operate = "新增";
			} else {
				operate = "修改";
			}
			System.out.println("成功" + operate + "了" + affectRowCount + "行");
			System.out.println();
		} catch (Exception e) {
			if (connection != null) {
				connection.rollback();
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return affectRowCount;
	}

	public static List<BS004> findAllBeSent() throws Exception {
		List<BS004> list = new ArrayList<BS004>();

		String sql = "SELECT\r\n" + "	ci.patient_code patientCode,\r\n" + "	ci.visit_no visitNo,\r\n"
				+ "	hd.dept_code ksCode,\r\n" + "	hd.dept_name ksName,\r\n" + "	dc.doctor_code operCode,\r\n"
				+ "	dc.doctor_name operName,\r\n" + "	ci.doctor_advice_id doctorAdviceId, ci.request_data,\r\n"
				+ "	ci.status, pe.bind_time bindTime,pe.unbind_time unBindTime\r\n" + "FROM\r\n"
				+ "	check_interface ci\r\n"
				+ "LEFT JOIN patient_examination pe ON ci.examination_code = pe.examination_code\r\n"
				+ "LEFT JOIN hospital_dept hd ON hd.dept_id = pe.operate_dept_id\r\n"
				+ "LEFT JOIN doctor dc ON dc.doctor_id = pe.operate_doctor_id\r\n" + "\r\n" + "WHERE\r\n"
				+ "	(ci.`status` - ci.mq_status) > 0";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/** 从数据库连接池中获取数据库连接 **/
			connection = DBConnectionPool.getInstance().getConnection();
			/** 执行SQL预编译 **/
			preparedStatement = connection.prepareStatement(sql);
//			System.out.format("sql = %s\r\n", sql);
			/** 执行sql **/
			ResultSet rs = preparedStatement.executeQuery();
			int i = 0;
			while (rs.next()) {
				BS004 bs004 = new BS004();
				bs004.setVisitNo(rs.getString("visitNo"));

				bs004.setPatientCode(rs.getString("patientCode"));

				bs004.setDoctorAdviceId(rs.getString("doctorAdviceId"));

				bs004.setKsCode(rs.getString("ksCode"));

				bs004.setKsName(rs.getString("ksName"));

				bs004.setOperCode(rs.getString("operCode"));

				bs004.setOperName(rs.getString("operName"));
				bs004.setStatus(rs.getString("status"));
				bs004.setRequestData(rs.getString("request_data"));

				String status = rs.getString("status");
				String bindTime = rs.getString("bindTime");
				String unBindTime = rs.getString("unBindTime");

				String opertime = TypeConvert.getOperTime(Integer.parseInt(status), bindTime, unBindTime);

				bs004.setOperTime(opertime);
				Map<String, String> p = TypeConvert.getStatus(Integer.parseInt(status));

				bs004.setDoctorAdviceCode(p.keySet().iterator().next());
				bs004.setDoctorAdviceName(p.get(p.keySet().iterator().next()));

				list.add(bs004);
				System.out.format("added to list: %s\r\n", ++i);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return list;
	}

	public static int findByDoctorAdviceId(String doctor_advice_id) throws Exception {
		/** 影响的行数 **/
		int total = 0;
		String sql = "SELECT count(*) as total FROM check_interface where doctor_advice_id = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/** 从数据库连接池中获取数据库连接 **/
			connection = DBConnectionPool.getInstance().getConnection();
			/** 执行SQL预编译 **/
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setObject(1, doctor_advice_id);
			/** 执行sql **/
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				total = rs.getInt("total");
			}
		} catch (Exception e) {
			if (connection != null) {
				connection.rollback();
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return total;
	}

	/**
	 * 获取所有的上报PDF
	 *
	 * @return
	 * @throws Exception
	 */
	public static List<BS320> findAllReportToSent() throws Exception {
		List<BS320> list = new ArrayList<BS320>();

		String sql = "SELECT\r\n"
				+ "	a.file_s3key fileS3Key,b.patient_code patientCode,b.visit_no visitNo, b.request_data\r\n"
				+ "FROM \r\n" + "	upload a\r\n" + "LEFT JOIN check_interface b ON a.file_s3key = b.cons_file_key\r\n"
				+ "WHERE\r\n" + "	uploaded = 0\r\n" + "AND b.check_id IS NOT NULL LIMIT 1";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/** 从数据库连接池中获取数据库连接 **/
			connection = DBConnectionPool.getInstance().getConnection();
			/** 执行SQL预编译 **/
			preparedStatement = connection.prepareStatement(sql);
			/** 执行sql **/
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				BS320 bs320 = new BS320();
				bs320.setFileS3Key(rs.getString("fileS3Key"));
				bs320.setPatientCode(rs.getString("patientCode"));
				bs320.setVisitNo(rs.getString("visitNo"));
				bs320.setRequestData(rs.getString("request_data"));
				list.add(bs320);
			}
		} catch (Exception e) {
			if (connection != null) {
				connection.rollback();
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return list;
	}

	/**
	 * 获取所有的上报未签名PDF(影像) zh new
	 * 
	 * @return
	 * @throws Exception
	 */
	public static List<BSImgBean> findAllReportImgToSent() throws Exception {
		List<BSImgBean> list = new ArrayList<BSImgBean>();

		String sql = "SELECT\r\n" + "	cr.cons_result_id cResultId,IFNULL(ci.visit_no,'')	visitNo,\r\n"
				+ "	IFNULL(b.bed_code,'') bedCode,\r\n" + "	IFNULL(b.patient_name,'') patientName,\r\n"
				+ "	IFNULL(b.sex,'') sex,\r\n" + "	IFNULL(b.age,'') age,\r\n"
				+ "	IFNULL(cr.cons_complete_time,'')  ccTime,\r\n" + "	IFNULL(doc.doctor_name,'') aDocName,\r\n"
				+ "	IFNULL(doc2.doctor_name,'') rDocName,\r\n" + "	IFNULL(cr.review_time,'') rTime,\r\n"
				+ "		IFNULL(ci.doctor_advice_id,'')  docAdviceId,\r\n"
				+ "	IFNULL( sign_report_key, cons_report_key ) filekey,\r\n"
				+ "	cr.upload_status,ci.request_data requestData	\r\n" + "FROM\r\n"
				+ "	`consultation_result` cr\r\n"
				+ "	LEFT JOIN consultation_apply ca ON cr.final_apply_id = ca.apply_id\r\n"
				+ "	LEFT JOIN patient_info b ON ca.patient_id = b.patient_id\r\n"
				+ "	LEFT JOIN patient_examination pe ON pe.examination_id = ca.examination_id\r\n"
				+ "	LEFT JOIN check_interface ci ON ci.examination_code = pe.examination_code\r\n"
				+ "	LEFT JOIN doctor doc ON doc.doctor_id = cr.cons_doctor_id\r\n"
				+ "	LEFT JOIN doctor doc2 ON doc2.doctor_id = cr.review_doctor_id \r\n" + "WHERE\r\n"
				+ "	ci.doctor_advice_id IS NOT NULL \r\n" + "	AND cr.upload_status = 0";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/** 从数据库连接池中获取数据库连接 **/
			connection = DBConnectionPool.getInstance().getConnection();
			connection.setAutoCommit(false);
			/** 执行SQL预编译 **/
			preparedStatement = connection.prepareStatement(sql);
			/** 执行sql **/
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				BSImgBean bsImg = new BSImgBean();
				bsImg.setaDocName(rs.getString("aDocName"));
				bsImg.setAge(rs.getString("age"));
				bsImg.setBedCode(rs.getString("bedCode"));
				bsImg.setCcTime(rs.getString("ccTime"));
				bsImg.setDocAdviceId(rs.getString("docAdviceId"));
				bsImg.setFilekey(rs.getString("filekey"));
				bsImg.setPatientName(rs.getString("patientName"));
				bsImg.setrDocName(rs.getString("rDocName"));
				bsImg.setrTime(rs.getString("rTime"));
				bsImg.setSex(rs.getString("sex"));
				bsImg.setVisitNo(rs.getString("visitNo"));
				bsImg.setRequestData(rs.getString("requestData"));
				bsImg.setcResultId(rs.getString("cResultId"));
				list.add(bsImg);
			}
		} catch (Exception e) {
			if (connection != null) {
				connection.rollback();
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return list;
	}
	
	
	
	
	/**
	 * 获取所有的上报已签名的PDF(影像) zh new
	 * 
	 * @return
	 * @throws Exception
	 */
	public static List<BSImgBean> findAllReportImgSignToSent() throws Exception {
		List<BSImgBean> list = new ArrayList<BSImgBean>();

		String sql = "SELECT\r\n" + "	cr.cons_result_id cResultId,IFNULL(ci.visit_no,'')	visitNo,\r\n"
				+ "	IFNULL(b.bed_code,'') bedCode,\r\n" + "	IFNULL(b.patient_name,'') patientName,\r\n"
				+ "	IFNULL(b.sex,'') sex,\r\n" + "	IFNULL(b.age,'') age,\r\n"
				+ "	IFNULL(cr.cons_complete_time,'')  ccTime,\r\n" + "	IFNULL(doc.doctor_name,'') aDocName,\r\n"
				+ "	IFNULL(doc2.doctor_name,'') rDocName,\r\n" + "	IFNULL(cr.review_time,'') rTime,\r\n"
				+ "		IFNULL(ci.doctor_advice_id,'')  docAdviceId,\r\n"
				+ "	IFNULL( sign_report_key, cons_report_key ) filekey,\r\n"
				+ "	cr.upload_status,ci.request_data requestData	\r\n" + "FROM\r\n"
				+ "	`consultation_result` cr\r\n"
				+ "	LEFT JOIN consultation_apply ca ON cr.final_apply_id = ca.apply_id\r\n"
				+ "	LEFT JOIN patient_info b ON ca.patient_id = b.patient_id\r\n"
				+ "	LEFT JOIN patient_examination pe ON pe.examination_id = ca.examination_id\r\n"
				+ "	LEFT JOIN check_interface ci ON ci.examination_code = pe.examination_code\r\n"
				+ "	LEFT JOIN doctor doc ON doc.doctor_id = cr.cons_doctor_id\r\n"
				+ "	LEFT JOIN doctor doc2 ON doc2.doctor_id = cr.review_doctor_id \r\n" + "WHERE\r\n"
				+ "	ci.doctor_advice_id IS NOT NULL \r\n" + "	AND cr.upload_status = 1 and  cr.sign_report_key is not null";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			/** 从数据库连接池中获取数据库连接 **/
			connection = DBConnectionPool.getInstance().getConnection();
			connection.setAutoCommit(false);
			/** 执行SQL预编译 **/
			preparedStatement = connection.prepareStatement(sql);
			/** 执行sql **/
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				BSImgBean bsImg = new BSImgBean();
				bsImg.setaDocName(rs.getString("aDocName"));
				bsImg.setAge(rs.getString("age"));
				bsImg.setBedCode(rs.getString("bedCode"));
				bsImg.setCcTime(rs.getString("ccTime"));
				bsImg.setDocAdviceId(rs.getString("docAdviceId"));
				bsImg.setFilekey(rs.getString("filekey"));
				bsImg.setPatientName(rs.getString("patientName"));
				bsImg.setrDocName(rs.getString("rDocName"));
				bsImg.setrTime(rs.getString("rTime"));
				bsImg.setSex(rs.getString("sex"));
				bsImg.setVisitNo(rs.getString("visitNo"));
				bsImg.setRequestData(rs.getString("requestData"));
				bsImg.setcResultId(rs.getString("cResultId"));
				list.add(bsImg);
			}
		} catch (Exception e) {
			if (connection != null) {
				connection.rollback();
			}
			e.printStackTrace();
			throw e;
		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
		return list;
	}
}
